const { getDbPool } = require('../infra/dbClient');
const db = getDbPool();
const { getLogger } = require('../logger');
const logger = getLogger('models.addressModel');

class AddressModel {
    // 获取地址列表
    static async getAddressList(page = 1, limit = 10, filters = {}) {
        const offset = (page - 1) * limit;
        let params = [];
        let whereClause = 'WHERE is_delete = 0';

        // 构建过滤条件
        if (filters.user_id) {
            whereClause += ' AND user_id = ?';
            params.push(filters.user_id);
        }
        
        if (filters.entity_type) {
            whereClause += ' AND entity_type = ?';
            params.push(filters.entity_type);
        }
        
        if (filters.entity_id) {
            whereClause += ' AND entity_id = ?';
            params.push(filters.entity_id);
        }
        
        if (filters.address_type) {
            whereClause += ' AND address_type = ?';
            params.push(filters.address_type);
        }
        
        if (filters.status !== undefined) {
            whereClause += ' AND status = ?';
            params.push(filters.status);
        }

        const sql = `
            SELECT 
                a.*,
                p.name AS province_name,
                c.name AS city_name,
                d.name AS district_name
            FROM address a
            LEFT JOIN provinces p ON a.province_id = p.id
            LEFT JOIN cities c ON a.city_id = c.id
            LEFT JOIN districts d ON a.district_id = d.id
            ${whereClause}
            ORDER BY a.updated_at DESC
            LIMIT ${db.escape(limit)} OFFSET ${db.escape(offset)}
        `;

        const countSql = `
            SELECT COUNT(*) AS count
            FROM address
            ${whereClause}
        `;

        try {
            const [addresses] = await db.execute(sql, params);
            const [[{ count }]] = await db.execute(countSql, params);
            
            return [addresses, count];
        } catch (error) {
            logger.error('获取地址列表失败', { error, filters });
            throw error;
        }
    }

    // 根据ID获取地址
    static async findById(id) {
        const sql = `
            SELECT 
                a.*,
                p.name AS province_name,
                c.name AS city_name,
                d.name AS district_name
            FROM address a
            LEFT JOIN provinces p ON a.province_id = p.id
            LEFT JOIN cities c ON a.city_id = c.id
            LEFT JOIN districts d ON a.district_id = d.id
            WHERE a.id = ? AND a.is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql, [id]);
            return rows[0] || null;
        } catch (error) {
            logger.error('根据ID获取地址失败', { error, id });
            throw error;
        }
    }

    // 创建地址
    static async create(addressData) {
        const { 
            user_id, entity_type, entity_id, 
            province_id, city_id, district_id, street_id,
            detail_address, postal_code, location_point,
            recipient_name, recipient_phone, recipient_email,
            address_type, address_tag, is_default,
            status, is_verified, created_by
        } = addressData;

        const sql = `
            INSERT INTO address (
                user_id, entity_type, entity_id,
                province_id, city_id, district_id, street_id,
                detail_address, postal_code, location_point,
                recipient_name, recipient_phone, recipient_email,
                address_type, address_tag, is_default,
                status, is_verified, created_by
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `;

        try {
            // 如果设置为默认地址，需要先将其他同类型地址设为非默认
            if (is_default && user_id) {
                await this.setOtherAddressesNonDefault(user_id, address_type, null);
            }

            const [result] = await db.execute(sql, [
                user_id, entity_type, entity_id,
                province_id, city_id, district_id, street_id,
                detail_address, postal_code, location_point,
                recipient_name, recipient_phone, recipient_email,
                address_type, address_tag, is_default,
                status, is_verified, created_by
            ]);
            
            return result.insertId;
        } catch (error) {
            logger.error('创建地址失败', { error, addressData });
            throw error;
        }
    }

    // 更新地址
    static async update(id, updateData) {
        const { 
            user_id, entity_type, entity_id, 
            province_id, city_id, district_id, street_id,
            detail_address, postal_code, location_point,
            recipient_name, recipient_phone, recipient_email,
            address_type, address_tag, is_default,
            status, is_verified, updated_by
        } = updateData;

        const fields = [];
        const params = [];

        // 构建更新字段和参数
        if (user_id !== undefined) { fields.push('user_id = ?'); params.push(user_id); }
        if (entity_type !== undefined) { fields.push('entity_type = ?'); params.push(entity_type); }
        if (entity_id !== undefined) { fields.push('entity_id = ?'); params.push(entity_id); }
        if (province_id !== undefined) { fields.push('province_id = ?'); params.push(province_id); }
        if (city_id !== undefined) { fields.push('city_id = ?'); params.push(city_id); }
        if (district_id !== undefined) { fields.push('district_id = ?'); params.push(district_id); }
        if (street_id !== undefined) { fields.push('street_id = ?'); params.push(street_id); }
        if (detail_address !== undefined) { fields.push('detail_address = ?'); params.push(detail_address); }
        if (postal_code !== undefined) { fields.push('postal_code = ?'); params.push(postal_code); }
        if (location_point !== undefined) { fields.push('location_point = ?'); params.push(location_point); }
        if (recipient_name !== undefined) { fields.push('recipient_name = ?'); params.push(recipient_name); }
        if (recipient_phone !== undefined) { fields.push('recipient_phone = ?'); params.push(recipient_phone); }
        if (recipient_email !== undefined) { fields.push('recipient_email = ?'); params.push(recipient_email); }
        if (address_type !== undefined) { fields.push('address_type = ?'); params.push(address_type); }
        if (address_tag !== undefined) { fields.push('address_tag = ?'); params.push(address_tag); }
        if (is_default !== undefined) { fields.push('is_default = ?'); params.push(is_default); }
        if (status !== undefined) { fields.push('status = ?'); params.push(status); }
        if (is_verified !== undefined) { fields.push('is_verified = ?'); params.push(is_verified); }
        if (updated_by !== undefined) { fields.push('updated_by = ?'); params.push(updated_by); }

        fields.push('updated_at = CURRENT_TIMESTAMP');
        params.push(id);

        const sql = `
            UPDATE address 
            SET ${fields.join(', ')}
            WHERE id = ? AND is_delete = 0
        `;

        try {
            // 如果设置为默认地址，需要先将其他同类型地址设为非默认
            if (is_default && user_id) {
                await this.setOtherAddressesNonDefault(user_id, address_type || await this.getAddressTypeById(id), id);
            }

            const [result] = await db.execute(sql, params);
            return result.affectedRows > 0;
        } catch (error) {
            logger.error('更新地址失败', { error, id, updateData });
            throw error;
        }
    }

    // 逻辑删除地址
    static async delete(id, deleted_by) {
        const sql = `
            UPDATE address 
            SET is_delete = 1, deleted_at = CURRENT_TIMESTAMP, updated_by = ?
            WHERE id = ? AND is_delete = 0
        `;

        try {
            const [result] = await db.execute(sql, [deleted_by, id]);
            return result.affectedRows > 0;
        } catch (error) {
            logger.error('删除地址失败', { error, id, deleted_by });
            throw error;
        }
    }

    // 批量删除地址
    static async deleteBatch(ids, deleted_by) {
        const placeholders = ids.map(() => '?').join(',');
        const sql = `
            UPDATE address 
            SET is_delete = 1, deleted_at = CURRENT_TIMESTAMP, updated_by = ?
            WHERE id IN (${placeholders}) AND is_delete = 0
        `;

        try {
            const [result] = await db.execute(sql, [deleted_by, ...ids]);
            return result.affectedRows;
        } catch (error) {
            logger.error('批量删除地址失败', { error, ids, deleted_by });
            throw error;
        }
    }

    // 设置其他同类型地址为非默认
    static async setOtherAddressesNonDefault(user_id, address_type, exclude_id) {
        let sql = `
            UPDATE address 
            SET is_default = 0 
            WHERE user_id = ? AND address_type = ? AND is_delete = 0 AND is_default = 1
        `;
        const params = [user_id, address_type];

        if (exclude_id) {
            sql += ' AND id != ?';
            params.push(exclude_id);
        }

        try {
            await db.execute(sql, params);
        } catch (error) {
            logger.error('设置其他地址为非默认失败', { error, user_id, address_type, exclude_id });
            throw error;
        }
    }

    // 根据ID获取地址类型
    static async getAddressTypeById(id) {
        const sql = `SELECT address_type FROM address WHERE id = ? AND is_delete = 0`;
        try {
            const [rows] = await db.execute(sql, [id]);
            return rows[0]?.address_type || null;
        } catch (error) {
            logger.error('获取地址类型失败', { error, id });
            return null;
        }
    }

    // 获取用户默认地址
    static async getDefaultAddress(user_id, address_type = null) {
        let sql = `
            SELECT 
                a.*,
                p.name AS province_name,
                c.name AS city_name,
                d.name AS district_name
            FROM address a
            LEFT JOIN provinces p ON a.province_id = p.id
            LEFT JOIN cities c ON a.city_id = c.id
            LEFT JOIN districts d ON a.district_id = d.id
            WHERE a.user_id = ? AND a.is_default = 1 AND a.is_delete = 0 AND a.status = 1
        `;
        const params = [user_id];

        if (address_type) {
            sql += ' AND a.address_type = ?';
            params.push(address_type);
        }

        try {
            const [rows] = await db.execute(sql, params);
            return rows[0] || null;
        } catch (error) {
            logger.error('获取默认地址失败', { error, user_id, address_type });
            return null;
        }
    }

    // 验证地址是否存在
    static async isExists(id) {
        const sql = `SELECT COUNT(*) AS count FROM address WHERE id = ? AND is_delete = 0`;
        try {
            const [[{ count }]] = await db.execute(sql, [id]);
            return count > 0;
        } catch (error) {
            logger.error('验证地址是否存在失败', { error, id });
            return false;
        }
    }
    
    // 获取所有省份列表
    static async getProvinceList() {
        const sql = `
            SELECT id, name, code, sort_order 
            FROM provinces 
            ORDER BY sort_order ASC
        `;

        try {
            const [rows] = await db.execute(sql);
            return rows;
        } catch (error) {
            logger.error('获取省份列表失败', { error });
            throw error;
        }
    }

    // 根据省份ID获取城市列表
    static async getCityList(provinceId) {
        const sql = `
            SELECT id, name, code, sort_order 
            FROM cities 
            WHERE province_id = ? 
            ORDER BY sort_order ASC
        `;

        try {
            const [rows] = await db.execute(sql, [provinceId]);
            return rows;
        } catch (error) {
            logger.error('获取城市列表失败', { error, provinceId });
            throw error;
        }
    }

    // 根据城市ID获取区县列表
    static async getDistrictList(cityId) {
        const sql = `
            SELECT id, name, code, sort_order 
            FROM districts 
            WHERE city_id = ? 
            ORDER BY sort_order ASC
        `;

        try {
            const [rows] = await db.execute(sql, [cityId]);
            return rows;
        } catch (error) {
            logger.error('获取区县列表失败', { error, cityId });
            throw error;
        }
    }
    
    // 通过省市区ID字符串获取嵌套的三层数据结构（返回label、value、children格式）
    static async getNestedAddressByStringIds(idString) {
        // 验证输入参数
        if (!idString || typeof idString !== 'string') {
            throw new Error('ID字符串不能为空');
        }
        
        // 将字符串分割为ID数组
        const ids = idString.split(',').map(id => parseInt(id.trim())).filter(id => !isNaN(id));
        
        if (ids.length < 1 || ids.length > 3) {
            throw new Error('ID字符串必须包含1-3个有效的数字ID');
        }
        
        const [provinceId, cityId, districtId] = ids;
        
        try {
            // 1. 查询省份信息
            const provinceSql = `SELECT id, name FROM provinces WHERE id = ?`;
            const [provinces] = await db.execute(provinceSql, [provinceId]);
            
            if (provinces.length === 0) {
                return null;
            }
            
            // 构建返回结果，使用label、value格式
            const result = {
                label: provinces[0].name,
                value: provinces[0].id,
                children: [],
                isLeaf: false // 省份不是叶子节点
            };
            
            // 2. 如果提供了城市ID，查询城市信息
            if (cityId) {
                const citySql = `SELECT id, name FROM cities WHERE id = ? AND province_id = ?`;
                const [cities] = await db.execute(citySql, [cityId, provinceId]);
                
                if (cities.length > 0) {
                    const cityObj = {
                        label: cities[0].name,
                        value: cities[0].id,
                        children: [],
                        isLeaf: false // 城市不是叶子节点
                    };
                    
                    // 3. 如果提供了区县ID，查询区县信息
                    if (districtId) {
                        const districtSql = `SELECT id, name FROM districts WHERE id = ? AND city_id = ?`;
                        const [districts] = await db.execute(districtSql, [districtId, cityId]);
                        
                        if (districts.length > 0) {
                            cityObj.children.push({
                                label: districts[0].name,
                                value: districts[0].id,
                                isLeaf: true // 区县是叶子节点
                                // 区县没有子级
                            });
                        }
                    }
                    
                    result.children.push(cityObj);
                }
            }
            
            return result;
        } catch (error) {
            logger.error('通过ID字符串获取嵌套地址数据失败', { error, idString });
            throw error;
        }
    }
}

module.exports = AddressModel;